﻿Imports System.Web

Public Class xml_loader

    'XML Loader for Magento Export by Piotr J Kula Licensed for KULA IT. Version 1.0 17/03/2010
    '----------
    'XML Loader is not the standard microsoft XML code
    'This loader is only intended for MAGENTO Export Function as MS Excel xml
    'This file should be one line long! Not formatted into seperate lines otherwise an array out of bounds will occur!
    'The very first <> should always contain <"?xml version="> version does not matter but should always be MAGENTO Formatted.

    Private fakeProgressBar As New ProgressBar
    Private openFileDialog1 As New OpenFileDialog()
    Private saveFileDialog1 As New SaveFileDialog()
    Private filename As String = ""
    Private TableXML As DataTable
    Private progress As Integer
    Private isLoaded As Boolean = False

    Public ReadOnly Property IS_LOADED() As Boolean
        Get
            Return isLoaded
        End Get
    End Property
    Public ReadOnly Property GET_TABLE() As DataTable
        Get
            Return TableXML
        End Get
    End Property

    Public WriteOnly Property SET_FILENAME() As String
        Set(ByVal value As String)
            filename = value
        End Set
    End Property

    Public ReadOnly Property GET_FILENAME() As String
        Get
            Return filename
        End Get
    End Property

    Public Sub loadTable()
        If filename.Equals("") Then
            load("")
        Else
            load(filename)
        End If
    End Sub

    Public ReadOnly Property GET_PROGRESS() As Integer
        Get
            Return progress
        End Get
    End Property

    Private Sub load(ByVal file_name As String)
        'Handle the file loading process either passed by value, or if no value use Open File Dialog.
        Try
            If file_name = "" Then
                openFileDialog1.Filter = "Magento Excel XML|*.xml"
                openFileDialog1.Title = "Open Magento Data File..."
                openFileDialog1.InitialDirectory = "c:\magento"
                If openFileDialog1.ShowDialog() <> DialogResult.OK Then
                    'MessageBox.Show("You did not specify a file name explicitly nor via File open.. Cannot Continue..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Exit Sub
                End If
                isLoaded = True
                filename = openFileDialog1.FileName
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try


        Dim XMLFileArray() As String

        'Dim X As Integer

        'Read XML File- Remove all brackets, sort into symetrical array header(?,?,?) , set 1(a,b,c), set 2(d,e,f), ... , set 3(x,y,z)
        XMLFileArray = System.IO.File.ReadAllLines(openFileDialog1.FileName)
        XMLFileArray = XMLFileArray(0).Split(CChar(">"))
        For i As Integer = 0 To XMLFileArray.Length - 1
            If XMLFileArray(i).Contains("<") Then
                XMLFileArray(i) = XMLFileArray(i).Replace(CChar("<"), "")
            End If
        Next
        '------------ XML Normalised and put into array

        'Time to find and map the data out
        Dim maxCellPerRow As Integer = 0 ' Number of cells found in first row (used as Column Names)

        Dim isRow As Boolean = False
        Dim currentColumn As Integer = 0
        Dim currentRow As Integer = -1
        Dim tableWidth As Integer = 0

        TableXML = New DataTable("MagentoXML")

        '' Read the XML document back in. Does not recognise this XML.. shit:(
        '' Create new FileStream to read schema with.
        'Dim fsReadXml As New System.IO.FileStream(openFileDialog1.FileName, System.IO.FileMode.Open)
        '' Create an XmlTextReader to read the file.
        'Dim myXmlReader As New System.Xml.XmlTextReader(fsReadXml)
        '' Read the XML document into the DataSet.
        'TableXML.ReadXml(myXmlReader)
        '' Close the XmlTextReader
        'myXmlReader.Close()


        'let organize array into a nice table
        Dim newrow As DataRow

        If XMLFileArray(0).Contains("?xml version=") Then 'check if file is XML type we need
            'First lets determine how many columns we have
            For i As Integer = 1 To XMLFileArray.Length - 1
                If XMLFileArray(i).Equals("Row") Then 'found a row with- now find data
                    isRow = True
                    currentRow += 1
                    newrow = TableXML.NewRow
                End If
                If XMLFileArray(i).Equals("/Row") Then 'end of row. reset row counters
                    isRow = False
                    tableWidth = currentColumn
                    currentColumn = 0
                    TableXML.Rows.Add(newrow)
                    TableXML.AcceptChanges()
                End If

                If isRow And currentRow = 0 And XMLFileArray(i).Contains("/Data") Then 'build the columns(header)
                    Dim c1 As New DataColumn(XMLFileArray(i).Remove(XMLFileArray(i).Length - 5))
                    TableXML.Columns.Add(c1)
                    currentColumn += 1
                End If

                If isRow And currentRow > 0 And XMLFileArray(i).Contains("/Data") Then 'build the rest of the table row by row
                    'For j As Integer = 0 To tableWidth
                    newrow(currentColumn) = XMLFileArray(i).Remove(XMLFileArray(i).Length - 5)
                    currentColumn += 1
                End If
            Next
        Else
            MessageBox.Show("This is not a Version 1.0 !Magento! Excel XML File!", "Error")
            Exit Sub
        End If

        TableXML.Rows.RemoveAt(0)

    End Sub

    Public Sub save_xml(ByVal _filename As String, ByVal _table As DataTable, ByRef _button As Button)


        Try
            If _filename = "" Then
                saveFileDialog1.Filter = "Magento Excel XML|*.xml"
                saveFileDialog1.Title = "Save Magento Data File..."
                saveFileDialog1.InitialDirectory = "c:\magento"
                If saveFileDialog1.ShowDialog() <> DialogResult.OK Then
                    'MessageBox.Show("You did not specify a file name explicitly nor via File open.. Cannot Continue..", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Exit Sub
                End If
                _filename = saveFileDialog1.FileName
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        Dim txt As System.Text.StringBuilder = New System.Text.StringBuilder

        txt.Append("<?xml version=""1.0""?><?mso-application progid=""Excel.Sheet""?><Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns:x2=""http://schemas.microsoft.com/office/excel/2003/xml"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:html=""http://www.w3.org/TR/REC-html40"" xmlns:c=""urn:schemas-microsoft-com:office:component:spreadsheet""><OfficeDocumentSettings xmlns=""urn:schemas-microsoft-com:office:office""></OfficeDocumentSettings><ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""></ExcelWorkbook><Worksheet ss:Name=""Sheet 1""><Table>")

        Dim stepper As Integer = 0

        'Write the header
        txt.Append("<Row>")
        For j As Integer = 0 To _table.Columns.Count - 1
            txt.Append("<Cell><Data ss:Type=""String"">" & _table.Columns(j).ToString & "</Data></Cell>")
        Next
        txt.Append("</Row>")

        'Write the Items
        For i As Integer = 0 To _table.Rows.Count - 1
            'open a row
            txt.Append("<Row>")

            'fill the rest of the cells as normal
            For j As Integer = 0 To _table.Columns.Count - 1
                txt.Append("<Cell><Data ss:Type=""String"">" & HttpUtility.HtmlEncode(_table.Rows(i).Item(j).ToString).Replace(vbCr, "").Replace(vbCrLf, "").Replace(vbLf, "") & "</Data></Cell>")
            Next

            txt.Append("</Row>")

            stepper += 1
            If _table.Rows.Count > 1 Then 'stop arithmitic overflow for one line 10% of 1 is .10 rounded up is 100%/1- divide error
                If stepper > CInt((_table.Rows.Count - 1) * 0.1) Then
                    'update built in progress counter'only on outer loop to increase performance.
                    progress = CInt(i / (_table.Rows.Count - 1) * 100)
                    '_progressbar.Value = progress
                    _button.Text = "Write File %" & progress
                    _button.Update()

                    stepper = 0
                End If

            End If

        Next
        txt.Append("</Table>")

        My.Computer.FileSystem.WriteAllText(_filename, txt.ToString, False)
 
        _button.Text = "Done"

    End Sub

End Class
